﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using uMES.LeanManufacturing.DBUtility;
using System.Data;
using uMES.LeanManufacturing.ParameterDTO;

namespace uMES.LeanManufacturing.ReportBusiness
{
    public class uMESContainerPrintBusiness
    {
        #region 001 批次列表
        #region 分页查询
        public uMESPagingDataDTO GetSourceData(Dictionary<string, string> para, int intPageIndex, int intPageSize)
        {
            string strSQL = GetSQL_D(para);

            uMESPagingDataDTO retR = OracleHelper.GetPagingDataIns(strSQL, intPageIndex, intPageSize);
            return retR;
        }
        #endregion

        #region 不分页查询，用于导出
        public DataTable GetAllDataForOutExcel(Dictionary<string, string> para)
        {
            string strSQL = GetSQL_D(para);

            DataTable DT = OracleHelper.GetDataTable(strSQL);
            return DT;
        }
        #endregion

        #region 组合查询语句
        protected string GetSQL_D(Dictionary<string,string> para)
        {
            StringBuilder strQuery = new StringBuilder();
            strQuery.AppendLine("SELECT c.containername,pb.productname,p.description,p.productrevision,c.qty,c.plannedstartdate,c.containerid,");
            strQuery.AppendLine("   wfs.workflowid,mo.processno,mo.oprno,c.plannedcompletiondate,c.productid,c.childcount,");
            strQuery.AppendLine("   wfb.workflowname,wf.workflowrevision AS workflowrev,wfs.workflowstepname AS stepname,");
            strQuery.AppendLine("   p.materialname,p.materialpaihao,p.materialguige,NVL(c.isaps,0) AS isaps,wfs.sequence,c.status,");
            strQuery.AppendLine("   c.holdreasonid,NVL(c.state,0) AS state,s.specid,c.containercomments");
            strQuery.AppendLine("FROM container c");
            strQuery.AppendLine("LEFT JOIN product p ON p.productid = c.productid");
            strQuery.AppendLine("LEFT JOIN productbase pb ON pb.productbaseid = p.productbaseid");
            strQuery.AppendLine("LEFT JOIN mfgorder mo ON mo.mfgorderid = c.mfgorderid");
            strQuery.AppendLine("LEFT JOIN currentstatus cu ON cu.currentstatusid = c.currentstatusid");
            strQuery.AppendLine("LEFT JOIN workflowstep wfs ON wfs.workflowstepid = cu.workflowstepid");
            strQuery.AppendLine("LEFT JOIN specbase sb ON sb.specbaseid=wfs.specbaseid");
            strQuery.AppendLine("LEFT JOIN spec s ON s.specid = NVL(sb.revofrcdid, wfs.specid)");
            strQuery.AppendLine("LEFT JOIN workflow wf ON wf.workflowid = wfs.workflowid");
            strQuery.AppendLine("LEFT JOIN workflowbase wfb ON wfb.workflowbaseid = wf.workflowbaseid");
            strQuery.AppendLine("WHERE 1 = 1  AND  c.finishstate IS NULL AND c.qty >0 --and c.status=1");
            strQuery.AppendLine("AND c.parentcontainerid IS NULL");

            if (para.Keys.Contains("IsAPS")) //是否已排产
            {
                if (!string.IsNullOrEmpty(para["IsAPS"]))
                {
                    //strQuery.AppendLine(string.Format("AND c.isaps = '{0}'", para["IsAPS"]));
                }
            }

            if (para.Keys.Contains("State")&& !string.IsNullOrEmpty(para["State"])) //根据状态筛选add:wangjh 1126
            {
                strQuery.AppendLine(string.Format("AND nvl(c.state,0) in ({0})", para["State"]));
            }

            if (para.Keys.Contains("MfgManagerid"))
            {
                if (!string.IsNullOrEmpty(para["MfgManagerid"]))
                {
                    strQuery.AppendLine(string.Format("AND mo.mfgmanagerid = '{0}'", para["MfgManagerid"]));
                }
            }

            if (para.Keys.Contains("ScanContainerName")) //扫描的批次号
            {
                if (!string.IsNullOrEmpty(para["ScanContainerName"]))
                {
                    strQuery.AppendLine(string.Format("AND c.containername = '{0}'", para["ScanContainerName"]));
                }
            }
            if (para.Keys.Contains("ProcessNo")) //工作令号
            {
                if (!string.IsNullOrEmpty(para["ProcessNo"]))
                {
                    strQuery.AppendLine(string.Format("AND LOWER(mo.processno) LIKE '%{0}%'", para["ProcessNo"].ToLower()));
                }
            }
            if (para.Keys.Contains("ContainerName")) //批次号
            {
                if (!string.IsNullOrEmpty(para["ContainerName"]))
                {
                    strQuery.AppendLine(string.Format("AND LOWER(c.containername) LIKE '%{0}%'", para["ContainerName"].ToLower()));
                }
            }
            if (para.Keys.Contains("ProductName")) //图号/名称
            {
                if (!string.IsNullOrEmpty(para["ProductName"]))
                {
                    strQuery.AppendLine(string.Format("AND (LOWER(pb.productname) LIKE '%{0}%' OR LOWER(p.description) LIKE '%{0}%')", para["ProductName"].ToLower()));
                }
            }
            if (para.Keys.Contains("ProductName2")&& !string.IsNullOrEmpty(para["ProductName2"])) //图号/名称
            {
                strQuery.AppendLine(string.Format("AND pb.productname='{0}' ", para["ProductName2"]));

            }
            if (para.Keys.Contains("ProductID")&& !string.IsNullOrEmpty(para["ProductID"])) //图号/名称
            {
                    strQuery.AppendLine(string.Format(" AND p.productid='{0}' ", para["ProductID"].ToString()));
                
            }
            if (para.Keys.Contains("StartDate")) //开始时间
            {
                if (!string.IsNullOrEmpty(para["StartDate"]))
                {
                    strQuery.AppendLine(string.Format("AND c.plannedstartdate >= TO_DATE('{0} 00:00:00','yyyy-MM-dd HH24:MI:SS')", para["StartDate"]));
                }
            }
            if (para.Keys.Contains("EndDate")) //结束时间
            {
                if (!string.IsNullOrEmpty(para["EndDate"]))
                {
                    strQuery.AppendLine(string.Format("AND c.plannedstartdate <= TO_DATE('{0} 23:59:59','yyyy-MM-dd HH24:MI:SS')", para["EndDate"]));
                }
            }

            if (para.Keys.Contains("ContainerID")) //
            {
                if (!string.IsNullOrEmpty(para["ContainerID"]))
                {
                    strQuery.AppendLine(string.Format("AND c.containerid = '{0}'", para["ContainerID"]));
                }
            }


            strQuery.AppendLine("ORDER BY pb.productname ASC");

            return strQuery.ToString();
        }
        #endregion
        #endregion

        #region 用于打印页面的查询 add:Wangjh 20210805
        public uMESPagingDataDTO GetContainerPrintData(Dictionary<string, string> para, int intPageIndex, int intPageSize)
        {
            StringBuilder strQuery = new StringBuilder();
            strQuery.AppendLine("SELECT c.containername,pb.productname,p.description,p.productrevision,c.qty,c.plannedstartdate,c.containerid,");
            strQuery.AppendLine("   wfs.workflowid,mo.processno,mo.oprno,c.plannedcompletiondate,c.productid,c.childcount,");
            strQuery.AppendLine("   wfb.workflowname,wf.workflowrevision AS workflowrev,wfs.workflowstepname AS stepname,");
            strQuery.AppendLine("   p.materialname,p.materialpaihao,p.materialguige,NVL(c.isaps,0) AS isaps,wfs.sequence,c.status,");
            strQuery.AppendLine("   c.holdreasonid,NVL(c.state,0) AS state,s.specid,c.containercomments,(select count(1) from ContainerPrintFrequency where containerid=c.containerid) printnum");
            strQuery.AppendLine("FROM container c");
            strQuery.AppendLine("LEFT JOIN product p ON p.productid = c.productid");
            strQuery.AppendLine("LEFT JOIN productbase pb ON pb.productbaseid = p.productbaseid");
            strQuery.AppendLine("LEFT JOIN mfgorder mo ON mo.mfgorderid = c.mfgorderid");
            strQuery.AppendLine("LEFT JOIN currentstatus cu ON cu.currentstatusid = c.currentstatusid");
            strQuery.AppendLine("LEFT JOIN workflowstep wfs ON wfs.workflowstepid = cu.workflowstepid");
            strQuery.AppendLine("LEFT JOIN specbase sb ON sb.specbaseid=wfs.specbaseid");
            strQuery.AppendLine("LEFT JOIN spec s ON s.specid = NVL(sb.revofrcdid, wfs.specid)");
            strQuery.AppendLine("LEFT JOIN workflow wf ON wf.workflowid = wfs.workflowid");
            strQuery.AppendLine("LEFT JOIN workflowbase wfb ON wfb.workflowbaseid = wf.workflowbaseid");
            strQuery.AppendLine("WHERE 1 = 1  AND  c.finishstate IS NULL AND c.qty >0 --and c.status=1");
            strQuery.AppendLine("AND c.parentcontainerid IS NULL");

            if (para.Keys.Contains("IsAPS")) //是否已排产
            {
                if (!string.IsNullOrEmpty(para["IsAPS"]))
                {
                    //strQuery.AppendLine(string.Format("AND c.isaps = '{0}'", para["IsAPS"]));
                }
            }

            if (para.Keys.Contains("State") && !string.IsNullOrEmpty(para["State"])) //根据状态筛选add:wangjh 1126
            {
                strQuery.AppendLine(string.Format("AND nvl(c.state,0) in ({0})", para["State"]));
            }

            if (para.Keys.Contains("MfgManagerid"))
            {
                if (!string.IsNullOrEmpty(para["MfgManagerid"]))
                {
                    strQuery.AppendLine(string.Format("AND mo.mfgmanagerid = '{0}'", para["MfgManagerid"]));
                }
            }

            if (para.Keys.Contains("ScanContainerName")) //扫描的批次号
            {
                if (!string.IsNullOrEmpty(para["ScanContainerName"]))
                {
                    strQuery.AppendLine(string.Format("AND c.containername = '{0}'", para["ScanContainerName"]));
                }
            }
            if (para.Keys.Contains("ProcessNo")) //工作令号
            {
                if (!string.IsNullOrEmpty(para["ProcessNo"]))
                {
                    strQuery.AppendLine(string.Format("AND LOWER(mo.processno) LIKE '%{0}%'", para["ProcessNo"].ToLower()));
                }
            }
            if (para.Keys.Contains("ContainerName")) //批次号
            {
                if (!string.IsNullOrEmpty(para["ContainerName"]))
                {
                    strQuery.AppendLine(string.Format("AND LOWER(c.containername) LIKE '%{0}%'", para["ContainerName"].ToLower()));
                }
            }
            if (para.Keys.Contains("ProductName")) //图号/名称
            {
                if (!string.IsNullOrEmpty(para["ProductName"]))
                {
                    strQuery.AppendLine(string.Format("AND (LOWER(pb.productname) LIKE '%{0}%' OR LOWER(p.description) LIKE '%{0}%')", para["ProductName"].ToLower()));
                }
            }
            if (para.Keys.Contains("ProductName2") && !string.IsNullOrEmpty(para["ProductName2"])) //图号/名称
            {
                strQuery.AppendLine(string.Format("AND pb.productname='{0}' ", para["ProductName2"]));

            }
            if (para.Keys.Contains("ProductID") && !string.IsNullOrEmpty(para["ProductID"])) //图号/名称
            {
                strQuery.AppendLine(string.Format(" AND p.productid='{0}' ", para["ProductID"].ToString()));

            }
            if (para.Keys.Contains("StartDate")) //开始时间
            {
                if (!string.IsNullOrEmpty(para["StartDate"]))
                {
                    strQuery.AppendLine(string.Format("AND c.plannedstartdate >= TO_DATE('{0} 00:00:00','yyyy-MM-dd HH24:MI:SS')", para["StartDate"]));
                }
            }
            if (para.Keys.Contains("EndDate")) //结束时间
            {
                if (!string.IsNullOrEmpty(para["EndDate"]))
                {
                    strQuery.AppendLine(string.Format("AND c.plannedstartdate <= TO_DATE('{0} 23:59:59','yyyy-MM-dd HH24:MI:SS')", para["EndDate"]));
                }
            }

            if (para.Keys.Contains("ContainerID")) //
            {
                if (!string.IsNullOrEmpty(para["ContainerID"]))
                {
                    strQuery.AppendLine(string.Format("AND c.containerid = '{0}'", para["ContainerID"]));
                }
            }


            strQuery.AppendLine("ORDER BY c.containerid desc,pb.productname ASC");
            

            uMESPagingDataDTO retR = OracleHelper.GetPagingDataIns(strQuery.ToString(), intPageIndex, intPageSize);
            return retR;
        }
        #endregion
    }
}
